﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class CheckInRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();
        BookingRepository m_bookingRep;
        RoomMasterRepository m_roomMasterRep;

        public CheckInRepository(MySqlConnection connection)
        {
            m_connection = connection;
            m_bookingRep = new BookingRepository(connection);
            aCommand.Connection = m_connection;
            m_roomMasterRep = new RoomMasterRepository(connection);
        }
        public void Insert(CheckIn checkin)
        {
            string insertSQL = String.Format(@"insert into table_checkin 
                (
                    checkin_code,
                    checkin_date,
                    customer_id,
                    roommaster_id,
                    checkin_disc,
                    checkin_start,
                    checkin_end,
                    checkin_status,
                    checkin_note,
                    user_id,
                    booking_id,
                    unit_id) 
                VALUES ('{0}','{1}',{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')",
                checkin.CODE, checkin.DATE.ToString(Tools.DATE_FORMAT_SHORT), checkin.CUSTOMER.ID, checkin.ROOM_MASTER.ID,
                checkin.DISCOUNT, checkin.START_DATE_TIME.ToString(Tools.DATE_FORMAT),
                checkin.END_DATE_TIME.ToString(Tools.DATE_FORMAT),checkin.STATUS,
                checkin.NOTE,checkin.USER.ID, checkin.BOOKING==null?0:checkin.BOOKING.ID, checkin.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            checkin.ID = GetMaximumId();

            if (checkin.BOOKING != null)
            {
                m_bookingRep.UpdateToClose(checkin.BOOKING.ID);
            }
        }
        public CheckIn GetById(int id)
        {
            string getByID = String.Format("select * from table_checkin where checkin_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            CheckIn checkin = transform(aReader);
            aReader.Close();
            return checkin;
        }
        public void Update(CheckIn checkin)
        {
            string updateSQL = String.Format(@"update table_checkin set 
                    checkin_code = '{0}',
                    customer_id = '{1}',
                    roommaster_id = '{2}',
                    checkin_disc = '{3}',
                    checkin_start = '{4}',
                    checkin_end = '{5}',
                    checkin_status = '{6}',
                    checkin_note = '{7}',
                    user_id = {8},
                    unit_id = {9},
                    checkin_date = '{10}',
                    booking_id = '{11}'
                where checkin_id = {12}",
                checkin.CODE, checkin.CUSTOMER.ID, checkin.ROOM_MASTER.ID,
                checkin.DISCOUNT, checkin.START_DATE_TIME.ToString(Tools.DATE_FORMAT), 
                checkin.END_DATE_TIME.ToString(Tools.DATE_FORMAT),
                checkin.STATUS, checkin.NOTE,checkin.USER.ID, checkin.UNIT.ID, 
                checkin.DATE.ToString(Tools.DATE_FORMAT_SHORT),
                checkin.BOOKING == null ? 0 : checkin.BOOKING.ID,
                checkin.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();

            if (checkin.BOOKING != null)
            {
                m_bookingRep.UpdateToClose(checkin.BOOKING.ID);
            }
        }
        public void Delete(CheckIn checkin)
        {
            string deleteSQL = "delete from table_checkin where checkin_id = " + checkin.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(checkin_id) from table_checkin");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_checkin where unit_id = {0}",unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int checkin)
        {
            string sql = String.Format("select * from table_checkin where unit_id = {0}", checkin);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllOpen(int unit)
        {
            string sql = String.Format("select * from table_checkin where checkin_status = 'OPEN' and unit_id = {0}", unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetCheckInInfo(IList rooms, DateTime date, int unit)
        {
            IList result = new ArrayList();
            //IList rms = m_roomMasterRep.GetAll(unit);
            result.Add(date.ToString("dd-MMM-yyyy HH:mm"));
            foreach (RoomMaster rm in rooms)
            {
                string sql = String.Format(@"SELECT 'CHECK_IN' FROM table_checkin t
                            where t.roommaster_id = {0}
                            and '{1}' between t.checkin_start and t.checkin_end
                            and t.checkin_status = 'OPEN'",rm.ID, date.ToString(Tools.DATE_FORMAT));
                aCommand.CommandText = sql;
                object r = aCommand.ExecuteScalar();
                string q = r == null ? "AVLBL" : "USED";

                if (q == "AVLBL")
                {
                    sql = String.Format(@"SELECT 'BOOKED_IN' FROM table_booking t
                            where t.roommaster_id = {0}
                            and '{1}' between t.booking_start and t.booking_end
                            and t.booking_status = 'OPEN'", rm.ID, date.ToString(Tools.DATE_FORMAT));
                    aCommand.CommandText = sql;
                    r = aCommand.ExecuteScalar();
                    q = r == null ? "AVLBL" : "BOOKED";
                }

                result.Add(q);
            }
            return result;
        }
        public IList GetAllSearch(string find, int unit)
        {
            string sql = String.Format(@"select t.* from table_checkin t, table_customer c where 
            concat(c.customer_code, c.customer_idno, c.customer_name,
                c.customer_address, c.customer_email, c.customer_phone, c.customer_job,
                c.customer_gender, t.checkin_code, 
                DATE_FORMAT(t.checkin_date, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.checkin_start, '%d-%m-%Y %H:%i:%s'),
                DATE_FORMAT(t.checkin_end, '%d-%m-%Y %H:%i:%s'),t.checkin_status
                ) like '%{0}%' and t.unit_id = {1}
                and t.customer_id = c.customer_id", find, unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private CheckIn transform(MySqlDataReader aReader)
        {
            CheckIn checkin = null;
            while (aReader.Read())
            {
                checkin = new CheckIn();
                checkin.ID = Convert.ToInt32(aReader["checkin_id"]);
                checkin.CODE = aReader["checkin_code"].ToString();
                checkin.CUSTOMER = new Customer(Convert.ToInt32(aReader["customer_id"]));
                checkin.ROOM_MASTER = new RoomMaster(Convert.ToInt32(aReader["roommaster_id"]));
                checkin.DISCOUNT = Convert.ToDouble(aReader["checkin_disc"]);
                checkin.START_DATE_TIME = Convert.ToDateTime(aReader["checkin_start"]);
                checkin.END_DATE_TIME = Convert.ToDateTime(aReader["checkin_end"]);
                checkin.STATUS = aReader["checkin_status"].ToString();
                checkin.NOTE = aReader["checkin_note"].ToString();
                checkin.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                checkin.USER = new User(Convert.ToInt32(aReader["user_id"]));
                checkin.DATE = Convert.ToDateTime(aReader["checkin_date"]);
                checkin.BOOKING = new Booking(Convert.ToInt32(aReader["booking_id"]));
            }
            return checkin;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                CheckIn checkin = new CheckIn();
                checkin.ID = Convert.ToInt32(aReader["checkin_id"]);
                checkin.CODE = aReader["checkin_code"].ToString();
                checkin.CUSTOMER = new Customer(Convert.ToInt32(aReader["customer_id"]));
                checkin.ROOM_MASTER = new RoomMaster(Convert.ToInt32(aReader["roommaster_id"]));
                checkin.DISCOUNT = Convert.ToDouble(aReader["checkin_disc"]);
                checkin.START_DATE_TIME = Convert.ToDateTime(aReader["checkin_start"]);
                checkin.END_DATE_TIME = Convert.ToDateTime(aReader["checkin_end"]);
                checkin.STATUS = aReader["checkin_status"].ToString();
                checkin.NOTE = aReader["checkin_note"].ToString();
                checkin.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                checkin.USER = new User(Convert.ToInt32(aReader["user_id"]));
                checkin.DATE = Convert.ToDateTime(aReader["checkin_date"]);
                checkin.BOOKING = new Booking(Convert.ToInt32(aReader["booking_id"]));
                result.Add(checkin);
            }
            return result;
        }
    }
}
